import json

import numpy as np
from itertools import combinations
from datetime import datetime
from mpi4py import MPI
import pymysql
from tensorflow import keras
from keras import regularizers
import time

import sys
import copy

sys.path.append('../../../')

from craft_all_five import bone, x1one, x2one,x3one, x4one, x5one, x6one, x7one, x8one, x9one, x10one, x11one, x12one, y1one, y2one, y3one, y4one, y5one

#from globalf import *
from splitglo import *
from neuralnet.shareglob import *

root = 0

#ddof=1	# coefficient of variation model
ddof=0

class cotblend(object):

   def cottonblending(self):
      combd=[]	# in this position, combd can be used directly for it's just a local variable in cottonblending function.
      coeffall=[]

      resisparse0=[]
      resisparsecb=[]

      cresisparse0=[]
      cresisparsecb=[]

      def cv(a):
         cvout=np.std(a, ddof)/np.mean(a)
         return cvout

###  		计算CV值，排除CV值不满足的原棉组合

      def cvariation(a):
         cvtest=False
         cv=np.std(a, ddof)/np.mean(a)
#         if (cv< 0.05):
         if (cv< 0.8):
           cvtest=True
         return cvtest

###     加载神经网络模型

      def multilayer_network():
      
          model=keras.models.Sequential()
          model.add(keras.layers.Dense(input_shape=(n_input,), units=n_hidden1, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
      #    model.add(keras.layers.Dense(n_hidden1, activation='sigmoid'))
          model.add(keras.layers.Dense(n_hidden2, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
          model.add(keras.layers.Dense(n_hidden3, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
          model.add(keras.layers.Dense(n_hidden4, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
          model.add(keras.layers.Dense(n_hidden5, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
          model.add(keras.layers.Dense(n_hidden6, kernel_regularizer=regularizers.l2(eta), activation='sigmoid'))
          model.add(keras.layers.Dense(n_output, activation=None))
      
          return model


###		原棉组合和配比系数进行加权平均，通过神经网络进行预测，并与纱线订单进行对比

      def trycomb(n_input, numper, residual, ncb, it, coefftmp, predict_data):
            cottonftry0=np.zeros(n_input)
#            cottonftry1=[]
         
            if (rank==numper):#for k in range(n_input):
             if (residual !=0):
              if (it < residual):#for k in range(n_input):
                 for k in range(n_input):
                     for i in range(ncb):
                         cottonftry0[k] += np.array(coefftmp[i]*combdeach[it][i][2][k])
#                     cottonftry1.append(cottonftry0)
                 cottonftry00=np.array([cottonftry0,cottonftry0])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    resisparse0.append(errtmp)
                    resisparsecb.append(coefftmp)

             else:
                 for k in range(n_input):
                     for i in range(ncb):
                         cottonftry0[k] += np.array(coefftmp[i]*combdeach[it][i][2][k])
           
                 cottonftry00=np.array([cottonftry0,cottonftry0])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    resisparse0.append(errtmp)
                    resisparsecb.append(coefftmp)
    
            else:
                 for k in range(n_input):
                     for i in range(ncb):
                         cottonftry0[k] += np.array(coefftmp[i]*combdeach[it][i][2][k])
#                     cottonftry1.append(cottonftry0)
           
                 cottonftry00=np.array([cottonftry0,cottonftry0])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    resisparse0.append(errtmp)
                    resisparsecb.append(coefftmp)
          
#            return resisparse0, resisparsecb

      cottonnum=[]


###	从数据库读取原棉所有种类

      def cndatamysql():
         filters=[]

         # 打开数据库连接
         db = pymysql.connect("172.16.34.71", "root", "HUAyuan123456!!!", "icdsdata")
         
         # 使用cursor()方法获取操作游标
         cursor = db.cursor()
         
         # SQL 查询语句
         sql = """
         SELECT
         `peimian_2019_distinct`.`公司内部批号`,
         `peimian_2019_distinct`.`产地`,
         `peimian_2019_distinct`.`马克隆值`,
         `peimian_2019_distinct`.`平均长度`,
         `peimian_2019_distinct`.`断裂比强度`,
         `peimian_2019_distinct`.`回潮率`,
         `peimian_2019_distinct`.`含杂率`,
         `peimian_2019_distinct`.`可纺系数`,
         `peimian_2019_distinct`.`成熟度`,
         `peimian_2019_distinct`.`整齐度`,
         `peimian_2019_distinct`.`短纤指数`,
         `peimian_2019_distinct`.`反射率`,
         `peimian_2019_distinct`.`黄度`,
         `peimian_2019_distinct`.`棉结`
         
         FROM
         `peimian_2019_distinct`
         """
         
         try:
             # 执行SQL语句
             cursor.execute(sql)
             # 获取所有记录列表
             resultsori = cursor.fetchall()
             # print(resultsori)
#             cottonalltest=[row for row in resultsori]
             cottonall=[row for row in resultsori]
        
                 # 打印结果
#             print('data = ', len(cottonalltest))
#             print('data = ', cottonalltest[0:10])
         except Exception as e:
             print("Error: unable to fetch data",e)
         
         # 关闭数据库连接
         db.close()
         if (rank==0):
            print('cottonall= ', cottonall[:][:][0])
            print('len(cottonall)= ', len(cottonall))
            print('len(cottonall[0])= ', len(cottonall[0]))

         craftnum=2
         dimcottx=len(cottonall[0])
         if (rank==0):
            print('if dimcottx + 1 is equal to n_input + 2 ?', dimcottx +1 ==n_input +2 )
         dimcott=len(cottonall)

#         cottonnum=[]
#         cottonnum=np.zeros((len(cottonall),len(cottonall[0])-2 +1,craftnum))
#         cottonnor=np.zeros((len(cottonall),len(cottonall[0])-2 +1,craftnum))
         cottonnew=np.zeros(( dimcott, dimcottx -2+1,craftnum))
         cottonnor=np.zeros(( dimcott, dimcottx -2+1,craftnum))

         for i in range(dimcott):
            for j in range(dimcottx-2):
              for k in range(craftnum):
                cottonnew[i][j][k]=np.array(eval(cottonall[i][j+2]))
#                cottonnew[i][j][1]=np.array(eval(cottonall[i][j+2]))
            cottonnew[i][dimcottx-2][0]=np.array(0.1)
            cottonnew[i][dimcottx-2][1]=np.array(0.5)
#            cottonnew[i][dimcottx-2][0]=np.array(0.5)
#            cottonnew[i][dimcottx-2][1]=np.array(0.1)
    
         if (rank==0):
            print('len(cottonnew) = ', len(cottonnew))
            print('len(cottonnew) = ', len(cottonnew[0]))
            print('len(cottonnew) = ', len(cottonnew[0][0]))

         for k in range(craftnum):
          cottoncraft=[]
          for i in range(dimcott):
            cottonpart=[]
#    is np.array() necessary?
            cottonpart.append(cottonall[i][0])
            cottonpart.append(cottonall[i][1])
#            cottonpart.append(np.array(cottonall[i][0]))
#            cottonpart.append(np.array(cottonall[i][1]))
            cottonpart.append(cottonnew[i,:,k])

            cottoncraft.append(cottonpart)
#          if (rank==0):
#             print('cottoncraft dim = ', len(cottoncraft))
#             print('cottoncraft shape = ', np.array(cottoncraft[0][2]).shape)

          cottonnum.append(cottoncraft)

         if (rank==0):
            print('cottonnum dim = ', len(cottonnum))
#            print('cottonnum dim = ', len(cottonnum[0]))
#            print('cottonnum dim = ', len(cottonnum[0][0]))
#            print('cottonnum dim = ', len(cottonnum[0][0][0]))
            print('cottonnum= ', cottonnum[0][:][2])

         cottonori=[ a for a in (bone, x1one, x2one, x3one, x4one, x5one, x6one, x7one, x8one, x9one, x10one, x11one, x12one)]

         for k in range(craftnum):
          for i in range(dimcott):
           for j in range(n_input):
             cottonnor[i,j,k]=(cottonnum[k][i][2][j] - min(cottonori[j]) - 0.02)/(max(cottonori[j])- min(cottonori[j])+ 0.05)

         if (rank==0):
            print('cottonnor dim = ', len(cottonnor))
            print('cottonnor shape = ', np.array(cottonnor).shape)

         ncb=num_cotton

         return ncb, craftnum

    
###	选取一定数量的原棉进行排列组合

      def choosecotton():
         filters=[]

         ncb=num_cotton
         speciesnum=num_cotton+2

#         speciesnum=13
         for k in range(speciesnum):
            filters.append(cottonchoose[k])

         if (rank==0):
            print('len(filters)= ', len(filters))

    
#         ncb=13
         if(ncb<10 and ncb>20):
            print('Cotton species should be in the range of 10-20')
            exit()


         for combina in combinations(filters,ncb):
             combd.append(combina)

#         print('combd[0]= ', combd[0], rank)   # works OK!!!
         
         if (rank==0):
            print('len(combd)= ',len(combd))
#            print('len(combd)= ',len(combd[0]))
#            print('len(combd)= ',len(combd[0][0]))
#            print('len(combd)= ',len(combd[0][0][2]))

    
###	所有原棉进行mpi并行处理
 
      def eachcott():
#         cottoneach=[]

         if (remainder==0):
            for it in range(cmpin):
             cottoneach.append(cottonnum[0][it + rank*cmpin])
   
         else:
          if(cresidual !=0):
           if(rank< cnumper):
            for it in range(cmpin):
            #for it in range(trange):
             cottoneach.append(cottonnum[0][it + rank*cmpin])
      
           if(rank== cnumper):
            for it in range(cresidual):
#                combdeach.append(combd[it+ numper*cmpin])
                cottoneach.append(cottonnum[0][it+ cnumper*cmpin])
   
          else:
           if(rank< cnumper):
            for it in range(cmpin):
             cottoneach.append(cottonnum[0][it + rank*cmpin])
   

###	调用神经网络模型对所有原棉进行预测排序

#      def sortcott(n_input, cnumper, cresidual, predict_data, it):
      def sortcott(n_input, predict_data, it):
            cottonftry0=np.zeros(n_input)
         
            if (rank==cnumper):#for k in range(n_input):
             if (cresidual !=0):
              if (it < cresidual):#for k in range(n_input):
                 cottonftry00=np.array([cottoneach[it][2],cottoneach[it][2]])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    cresisparse0.append(errtmp)

             else:
           
                 cottonftry00=np.array([cottoneach[it][2],cottoneach[it][2]])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    cresisparse0.append(errtmp)
    
            else:
                 cottonftry00=np.array([cottoneach[it][2],cottoneach[it][2]])
                 predout0=model.predict(cottonftry00)
                 errtmp=(np.linalg.norm(predout0[0][:] - predict_data[:]))/np.linalg.norm(predict_data[:])
                 if errtmp<10.3:
                    cresisparse0.append(errtmp)

 
###	对所有的原棉排列组合进行mpi并行

      def eachfun():
   
         if (remainder==0):
            for it in range(mpin):
             combdeach.append(combd[it + rank*mpin])
   
         else:
          if(residual !=0):
           if(rank< numper):
            for it in range(mpin):
             combdeach.append(combd[it + rank*mpin])
      
           if(rank== numper):
            for it in range(residual):
                combdeach.append(combd[it+ numper*mpin])
   
          else:
           if(rank< numper):
            for it in range(mpin):
             combdeach.append(combd[it + rank*mpin])
   

      ncb, craftnum=cndatamysql()


      cottonallnum=len(cottonnum[0])
      print('cottonallnum = ', cottonallnum)

      remainder=cottonallnum % size

      if(remainder==0):
         cmpin=int(cottonallnum/size)
         cnumper=int(cottonallnum/cmpin)
         cresidual=cottonallnum%cmpin

      else:
         cmpin=int(cottonallnum/size)+1
         cresidual=cottonallnum%cmpin
         cnumper=int(cottonallnum/cmpin)


#      ndiscrete=2
      ndiscrete=4
      ncott=ncb
      ndisall=ndiscrete**(ncott-1)
      
      # discrete step
      xx = np.zeros((ncb, ndiscrete))
     
      cottonkpi=np.zeros((n_input, craftnum)) 
#      resisparseall=[]
      costall=[]
#      resisparseallcb=[]
      resivalue=[]
    

      textilelist=np.array([40., 16., 87., 335., 98.])
      npred=len(textilelist)#predresu=[]
#      predict_data=textilelist
      predict_data=[a for a in (Yarn_count, Yarn_strength, Actual_twist, Actual_twist_factor, Twisting_efficiency)]
      if (rank==0):
         print('predict textile original= ', predict_data)

      yall=[]
      for a in (y1one, y2one, y3one, y4one, y5one):
        yall.append(a)
      for i in range(npred):
        predict_data[i]=(predict_data[i]-min(yall[i])+0.005)/(max(yall[i])-min(yall[i])+0.01)	# yarn strength

      if (rank==0):
         print('predict textile original= ', predict_data)

 
      model=multilayer_network()
#      print('textilenum= ', textilenum)
      if (textilenum >= 9. and textilenum <= 11.):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-10.hdf5')
      elif (textilenum > 11. and textilenum <= 15.):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-10-15.hdf5')
      elif (textilenum > 15. and textilenum <= 19.5):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-15-20.hdf5')
      elif (textilenum > 19.5 and textilenum <= 24.):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-20-24.hdf5')
      elif (textilenum > 24. and textilenum <= 29.5):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-25-30.hdf5')
      elif (textilenum > 29.5 and textilenum <= 35.):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-30-35.hdf5')
      elif (textilenum > 35. and textilenum <= 40.5):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-35-40.hdf5')
      #         model.load_weights('/home/hy/icds_new/info/modules/api/weights-biases-all-keras.hdf5')
      elif (textilenum > 40. and textilenum <= 59.5):
          model.load_weights('/home/hy/icds_new/weights-biases-out5-40.hdf5')


      combdeach=[]
      cottoneach=[]
#      coeffall_each=[]
      resisparseeach=[]
      resisparseeachcb=[]
      cresisparseeach=[]


      eachcott()
      if (len(cottoneach)>0):#for it in range(mpin):
         for it in range(cmpin):

             sortcott(n_input, predict_data, it)

      csendbuf=np.array(cresisparse0).reshape(-1)
      csendcounts=np.array(comm.gather(len(csendbuf), root))

# sendcounts only works in root process
      if rank==0:
         print('sendrecv counts 1= ',csendcounts, rank)
#         print('sendrecv counts 3= ',recv_data, rank, it)

     # resisparse_gather=None
      if rank==0:
         crecvbuf=np.empty(sum(csendcounts), dtype='d' )
      else:
         crecvbuf=None

      comm.Gatherv(sendbuf=csendbuf, recvbuf=(crecvbuf, csendcounts), root=0)

      cottonresi=crecvbuf
      corderindex= np.argsort(cottonresi)

      if (rank==0):
         print('corderindex= ', corderindex)#= np.argsort(cottonresi)

      zorderindex=comm.bcast( corderindex if rank == 0 else None, root=0 )


      cottonchoose=[cottonnum[0][zorderindex[k]] for k in range(len(zorderindex))]

      choosecotton()


      trange=len(combd)
      remainder=trange % size

      if (remainder==0):
         mpin=int(trange/size)
         numper=int(trange/mpin)
         residual=trange%mpin

      else:
         mpin=int(trange/size)+1
         residual=trange%mpin
         numper=int(trange/mpin)

      if (rank==0):
         print('len(combd) new= ', len(combd))
#         print('combd new= ', combd)


      delta = 1. / ndiscrete / 4.
      startd = 1. / ndiscrete / 4.
      if (ncb > 13):  # startd=1./ndiscrete/4.
          startd = 1. / 20.
          delta = 1. / 20.
  
      if (ncb > 17):  # startd=1./ndiscrete/4.
          startd = 1. / 25.
          #            delta =1./25.
          delta = 1. / 25. * 1.2


#      if rank ==0:
      identify = str(ncb) + str(delta) + str(startd) + str(ndiscrete)

      # 打开数据库连接
      db = pymysql.connect("172.16.34.71", "root", "HUAyuan123456!!!", "icds_api")

      # 使用cursor()方法获取操作游标
      cursor = db.cursor()

      # SQL 查询语句
      sql = """SELECT
      COEFFICIENT.Array
      FROM
      COEFFICIENT
      WHERE
      COEFFICIENT.Identify = %s""" % "\"" + identify +"\""

      try:
          # 执行SQL语句
          cursor.execute(sql)
          # 获取所有记录列表
          results = cursor.fetchall()

          results = json.loads(results[0][0])


      except Exception as e:
        print("Error: unable to fetch data", e)

      # 关闭数据库连接
      db.close()

      if rank==0:
          print('results=', len(results))

      if results == ():

#          from coeffall_ga import coeffallfun
          from coeffall_all import coeffallfun
          coeffallfun(ndiscrete,ncb,xx,coeffall, delta, startd)

          if(rank==0):
             print('coeffall from coeffallfun() : ', len(coeffall))

          sendbufcoe=np.array(coeffall).reshape(-1)
          sendcountscoe=np.array(comm.gather(len(sendbufcoe), root))

    # sendcounts only works in root process
          if rank==0:
             print('sendrecv counts 2= ',sendcountscoe, rank)#, it)

          if rank==0:
             recvbufcoe=np.empty(sum(sendcountscoe), dtype='d' )
          else:
             recvbufcoe=None

          comm.Gatherv(sendbuf=sendbufcoe, recvbuf=(recvbufcoe, sendcountscoe), root=0)

          if rank==0:
             zcoeffall_gather=recvbufcoe
             print('times of ncb, num of coeffall_gather = ', len(zcoeffall_gather))
             print(' coeffall_gather[0] before reshape = ', zcoeffall_gather[0])

          coeffall_gather=comm.bcast( zcoeffall_gather if rank == 0 else None, root=0 )

          numgather=int(len(coeffall_gather)/ncb)

          coeffall_gather=[coeffall_gather[ncb*i:(ncb*(i+1))] for i in range(numgather)]

          if rank ==0:
              # 打开数据库连接
              db = pymysql.connect("172.16.34.71", "root", "HUAyuan123456!!!", "icds_api")
              # 使用cursor()方法获取操作游标
              cursor = db.cursor()
              identify = str(ncb) + str(delta) + str(startd) + str(ndiscrete)
              array_data = coeffall_gather
              sql2 = """insert into COEFFICIENT(Identify,Array) values(%s,%s)""" % ("\"" + identify+ "\"","\"" + str(np.array(array_data).tolist()) + "\"")
              try:
                cursor.execute(sql2.encode('utf8'))
                # 提交
                db.commit()

              except Exception as e:
                print(e,"-----------=-=-=-=-=-=")

              db.close()
      else:

          coeffall_gather = [np.array(results[i]) for i in range(len(results))]


      eachfun()
     
      if rank==0:
         cresisparse_gather=crecvbuf
         print('len(cresisparse_gather)=', len(cresisparse_gather))

      if rank==0:
        corderindex= np.argsort(cresisparse_gather)
        print('corderindex= ', corderindex)

#      print('len(combdeach)= ', len(combdeach), rank )#for it in range(mpin):
#      print('combdeach= ', combdeach, rank )#for it in range(mpin):

      if (len(combdeach)>0):#for it in range(mpin):
         for it in range(mpin):

          resisparseall=[]
          resisparseallcb=[]
          cresisparseall=[]

          if (rank==0): 
             print('test number= ',it)

#          if ((cottonkpi == True).all()):
          if True:
             numeachcoeff=len(coeffall_gather)
             for i in range(numeachcoeff):
                coefftmp=coeffall_gather[i]
                trycomb(n_input, numper, residual, ncb, it, coefftmp, predict_data)

          if (rank==numper):#for k in range(n_input):
           if (residual !=0):
            if (it < residual):#for k in range(n_input):
               if (len(resisparse0)>0):
#               if (len(resisparseall_gather)>0):
                  resisparsemin=min(resisparse0)
                  indextmpmin=np.where(resisparse0==min(resisparse0))
          
                  cbmintmp=resisparsecb[indextmpmin[0][0]]
                 
               resisparseeach.append(resisparsemin)
               resisparseeachcb.append(cbmintmp)
 
           else:
               if (len(resisparse0)>0):
                  resisparsemin=min(resisparse0)
                  indextmpmin=np.where(resisparse0==min(resisparse0))
          
                  cbmintmp=resisparsecb[indextmpmin[0][0]]
                 
               resisparseeach.append(resisparsemin)
               resisparseeachcb.append(cbmintmp)
         
          else:
               resisparsemin=[]
               cbmintmp=[]
               if (len(resisparse0)>0):
                  resisparsemin=min(resisparse0)
                  indextmpmin=np.where(resisparse0==min(resisparse0))
          
                  cbmintmp=resisparsecb[indextmpmin[0][0]]
                 
               resisparseeach.append(resisparsemin)
               resisparseeachcb.append(cbmintmp)


        
      sendbuf=np.array(resisparseeach).reshape(-1)
      sendcounts=np.array(comm.gather(len(sendbuf), root))
     
      sendbufcoe=np.array(resisparseeachcb).reshape(-1)
      sendcountscoe=np.array(comm.gather(len(sendbufcoe), root))

      send_data = rank                                                    
#      print "process {} send data {} to root...".format(rank, send_data)  
      recv_data = comm.gather(send_data, root=0)

# sendcounts only works in root process
      if rank==0:
         print('sendrecv counts 1= ',sendcounts, rank, it)
         print('sendrecv counts 2= ',sendcountscoe, rank, it)
#         print('sendrecv counts 3= ',recv_data, rank, it)
     
     # resisparse_gather=None
      if rank==0:
         recvbuf=np.empty(sum(sendcounts), dtype='d' )
         recvbufcoe=np.empty(sum(sendcountscoe), dtype='d' )
      else:
         recvbuf=None
         recvbufcoe=None
     
      comm.Gatherv(sendbuf=sendbuf, recvbuf=(recvbuf, sendcounts), root=0)
      comm.Gatherv(sendbuf=sendbufcoe, recvbuf=(recvbufcoe, sendcountscoe), root=0)
     
      if rank==0:
         resisparse_gather=recvbuf
         resisparsecoe_gather=recvbufcoe
         print('len(resisparse_gather)=', len(resisparse_gather))


      if (rank==0):
        resimin=min(resisparse_gather)
        print('resimin= ',resimin)
        print('resisparse_gather length = ',len(resisparse_gather))
#        print('rank_gather = ',resisparserank_gather)
        indexnmin=np.where(resisparse_gather==min(resisparse_gather))
        print('resimin index= ',indexnmin)
#        print('resimin index= ',indexnmin[0][0])
        numgather=len(resisparse_gather)
        resisparsecoe_gather=[resisparsecoe_gather[ncb*i:(ncb*(i+1))] for i in range(numgather)]
#        print('resisparsecoe_gather = ',resisparsecoe_gather)
        print('resisparsecoe_gather length = ', len(resisparsecoe_gather))

#        print('resisparse_gather= ',resisparse_gather)
#        print('resisparsecoe_gather= ', resisparsecoe_gather)
        amin=resisparsecoe_gather[indexnmin[0][0]]
        print('amin shape= ',np.array(amin).shape)
        print('index of minimum of abs error=',amin)
        #print('valuemin= ', resivalue[0])
##        valuemin=resivalue[indexnmin[0][0]]
##        print('value of cotton = ', valuemin)
        
        cottonpred=np.zeros( n_input)
        for k in range(n_input):
           for i in range(ncb):
              cottonpred[k] += np.array(np.array(amin[i])*combd[indexnmin[0][0]][i][2][k])
   
        print('cottonpred  ', cottonpred[:])
   
   #     for k in range(ncb):
        print('cottonpred ori= ', combd[indexnmin[0][0]][:])
        
        cotton_data = combd[indexnmin[0][0]][:]

        orderindex= np.argsort(resisparse_gather)
        print('orderindex= ', orderindex)

        if (trange== numgather):
           pass;
#           print('trange is equal to numgather')
        else:
           exit()

        trycotton=[]
        trycoeff=[]
        trynum=5

        for it in range(numgather):

           for k in range(n_input):
              cottontmp=[]
              for i in range(ncb):
                  cottontmp.append(combd[it][i][2][k])
#                  cottontmp.append(combdeach[it][i][2][k])
              cottonkpi[k][0]=cvariation(np.array(cottontmp))
              cottonkpi[k][1]=cvariation(np.array(cottontmp))

           if ((cottonkpi == True).all()):
               trycotton.append(combd[orderindex[it]])
               trycoeff.append(resisparsecoe_gather[orderindex[it]])

           if(len(trycotton)>=trynum):
              break;

        trycottaver=[]

        for it in range(trynum):
           cottonaver=np.zeros(n_input)
           for k in range(n_input):
               for i in range(ncb):
                   cottonaver[k] += np.array(trycoeff[it][i]*trycotton[it][i][2][k])
           trycottaver.append(cottonaver)


        cottontrynum=np.array(trycottaver)
        textilepred=model.predict(cottontrynum)
        print('len(textilepred)= ', len(textilepred))
#        print('(textilepred)= ', textilepred)

        textileori=[ a for a in (y1one, y2one, y3one, y4one, y5one)]

#        textilepredn=np.array((trynum, n_output))
        for i in range(trynum):
           for j in range(n_output):
             textilepred[i,j]=textilepred[i][j]*(max(textileori[j])- min(textileori[j])+ 0.01) + min(textileori[j]) - 0.005
        print('textilepred= ', textilepred)


        print('len(trycotton)= ', len(trycotton))
        print('combd[orderindex]= ', combd[orderindex[0]], '\n', resisparsecoe_gather[orderindex[0]])


#        numsort=len(trycotton)
        cotton_recom= [trycotton[i] for i in range(trynum)]

#        print('cotton_recom= ', cotton_recom)

        # 打开数据库连接
        db = pymysql.connect("172.16.34.71", "root", "HUAyuan123456!!!", "icds_api")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        Group_id = time.time()
        User = "admin"
        Craft = "JC"
        sql2 = """insert into OUT_PUT_GROUP(User,Craft,Final_calculation_time,Group_id) values(%s,%s,%s,%d)""" % (
            "\"" + User + "\"", "\"" + Craft + "\"", "\"" + str(datetime.now()) + "\"", Group_id)
        cursor.execute(sql2.encode('utf8'))
        Package_id = 0
        for cotton_infos in trycotton:
            Package_id = Package_id + 1
            for cotton_info, Scale_factor in zip(cotton_infos, amin):
                # SQL 查询语句
                Batch_number = cotton_info[0]
                Production_place = cotton_info[1]
                Micronaire = cotton_info[2][0]
                Average_length = cotton_info[2][1]
                Fibre_strength = cotton_info[2][2]
                Moisture_regain = cotton_info[2][3]
                Trash_content = cotton_info[2][4]
                Spinning_coefficient = cotton_info[2][5]
                Maturity = cotton_info[2][6]
                Uniformity = cotton_info[2][7]
                Fiber_index = cotton_info[2][8]
                Reflectivity = cotton_info[2][9]
                Yellowness_index = cotton_info[2][10]
                Neps = cotton_info[2][11]


                sql = """insert into GET_OUT_PUT(Scale_factor,Batch_number,Production_place,Micronaire,Average_length,Fibre_strength,
                                    Moisture_regain,Trash_content,Spinning_coefficient,Maturity,Uniformity,Fiber_index,
                                    Reflectivity,Yellowness_index,Neps,Group_id,Package_id) values(%.4f,%s,%s,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,
                                    %.2f,%.2f,%d,%d,%d,%d,%d)""" % (Scale_factor,
                                                                 "\"" + Batch_number + "\"",
                                                                 "\"" + Production_place + "\"", float(Micronaire),
                                                                 float(Average_length), float(Fibre_strength),
                                                                 float(Moisture_regain), float(Trash_content),
                                                                 float(Spinning_coefficient),
                                                                 float(Maturity), float(Uniformity),
                                                                 float(Fiber_index),
                                                                 Reflectivity, Yellowness_index, Neps, Group_id,Package_id)

                try:
                    cursor.execute(sql.encode('utf8'))
                    # 提交
                    db.commit()

                except Exception as e:
                    print(e)

        db.close()

#cottbrun=cotblend()
#cottbrun.cottonblending()
